# -*- coding: utf-8 -*-
"""
销售数据访问层

负责销售相关的数据库操作
"""
from typing import List, Dict, Any, Optional
from .base_repository import BaseRepository
import logging

logger = logging.getLogger(__name__)

class SalesRepository(BaseRepository):
    """销售数据访问类"""
    
    def __init__(self):
        super().__init__("trade_order_goods")
    
    def get_goods_sales_statistics(self, goods_id: Optional[str] = None, 
                                 page: int = 1, page_size: int = 10,
                                 start_date: Optional[str] = None,
                                 end_date: Optional[str] = None) -> Dict[str, Any]:
        """查询商品销售统计"""
        where_conditions = ["goods_id IS NOT NULL", "goods_id != ''"]
        params = []
        
        if goods_id:
            where_conditions.append("goods_id = %s")
            params.append(goods_id)
            
        if start_date:
            where_conditions.append("created >= %s")
            params.append(start_date)
            
        if end_date:
            where_conditions.append("created <= %s")
            params.append(end_date)
        
        where_clause = "WHERE " + " AND ".join(where_conditions)
        
        # 计算总数
        count_sql = f"""
            SELECT COUNT(DISTINCT goods_id) as total
            FROM trade_order_goods
            {where_clause}
        """
        
        total_result = self.execute_custom_query(count_sql, params, True)
        total = total_result[0]['total'] if total_result else 0
        
        # 获取分页数据
        offset = (page - 1) * page_size
        sales_sql = f"""
            SELECT 
                goods_id,
                goods_name,
                spec_id,
                spec_name,
                SUM(CAST(IFNULL(num, 0) AS UNSIGNED)) as total_sales_quantity,
                SUM(CAST(IFNULL(total_amount, 0) AS DECIMAL(10,2))) as total_sales_amount,
                SUM(CAST(IFNULL(paid, 0) AS DECIMAL(10,2))) as total_paid_amount,
                COUNT(*) as order_count,
                AVG(CAST(IFNULL(price, 0) AS DECIMAL(10,2))) as avg_price,
                MAX(created) as last_sale_date,
                MIN(created) as first_sale_date
            FROM trade_order_goods
            {where_clause}
            GROUP BY goods_id, goods_name, spec_id, spec_name
            ORDER BY total_sales_quantity DESC
            LIMIT %s OFFSET %s
        """
        
        params.extend([page_size, offset])
        sales_data = self.execute_custom_query(sales_sql, params, True) or []
        
        return {
            "total": total,
            "items": sales_data
        }
    
    def get_single_goods_sales_detail(self, goods_id: str) -> Dict[str, Any]:
        """查询单个商品的销售详情"""
        sql = """
            SELECT 
                goods_id,
                goods_name,
                spec_id,
                spec_name,
                SUM(CAST(IFNULL(num, 0) AS UNSIGNED)) as total_sales_quantity,
                SUM(CAST(IFNULL(total_amount, 0) AS DECIMAL(10,2))) as total_sales_amount,
                SUM(CAST(IFNULL(paid, 0) AS DECIMAL(10,2))) as total_paid_amount,
                COUNT(*) as order_count,
                AVG(CAST(IFNULL(price, 0) AS DECIMAL(10,2))) as avg_price,
                MAX(created) as last_sale_date,
                MIN(created) as first_sale_date
            FROM trade_order_goods
            WHERE goods_id = %s
            GROUP BY goods_id, goods_name, spec_id, spec_name
            ORDER BY total_sales_quantity DESC
        """
        
        result = self.execute_custom_query(sql, [goods_id], True)
        return result if result else []
    
    def get_top_selling_goods(self, limit: int = 10, 
                            start_date: Optional[str] = None,
                            end_date: Optional[str] = None) -> List[Dict[str, Any]]:
        """查询销售量排行榜"""
        where_conditions = ["goods_id IS NOT NULL", "goods_id != ''"]
        params = []
        
        if start_date:
            where_conditions.append("created >= %s")
            params.append(start_date)
            
        if end_date:
            where_conditions.append("created <= %s")
            params.append(end_date)
        
        where_clause = "WHERE " + " AND ".join(where_conditions)
        
        sql = f"""
            SELECT 
                goods_id,
                goods_name,
                SUM(CAST(IFNULL(num, 0) AS UNSIGNED)) as total_sales_quantity,
                SUM(CAST(IFNULL(total_amount, 0) AS DECIMAL(10,2))) as total_sales_amount,
                COUNT(*) as order_count,
                AVG(CAST(IFNULL(price, 0) AS DECIMAL(10,2))) as avg_price
            FROM trade_order_goods
            {where_clause}
            GROUP BY goods_id, goods_name
            ORDER BY total_sales_quantity DESC
            LIMIT %s
        """
        
        params.append(limit)
        result = self.execute_custom_query(sql, params, True) or []
        return result
    
    def get_sales_summary(self, start_date: Optional[str] = None,
                         end_date: Optional[str] = None) -> Dict[str, Any]:
        """获取销售汇总数据"""
        where_conditions = ["goods_id IS NOT NULL", "goods_id != ''"]
        params = []
        
        if start_date:
            where_conditions.append("created >= %s")
            params.append(start_date)
            
        if end_date:
            where_conditions.append("created <= %s")
            params.append(end_date)
        
        where_clause = "WHERE " + " AND ".join(where_conditions)
        
        sql = f"""
            SELECT 
                COUNT(DISTINCT goods_id) as total_goods_count,
                SUM(CAST(IFNULL(num, 0) AS UNSIGNED)) as total_quantity_sold,
                SUM(CAST(IFNULL(total_amount, 0) AS DECIMAL(10,2))) as total_sales_amount,
                SUM(CAST(IFNULL(paid, 0) AS DECIMAL(10,2))) as total_paid_amount,
                COUNT(DISTINCT tid) as total_orders,
                AVG(CAST(IFNULL(price, 0) AS DECIMAL(10,2))) as avg_unit_price
            FROM trade_order_goods
            {where_clause}
        """
        
        result = self.execute_custom_query(sql, params, True)
        return result[0] if result else {} 
    
    def get_top_products(self, period: str = 'month', limit: int = 10) -> List[Dict]:
        """获取热销商品排行"""
        try:
            # 根据时间段构建查询条件
            if period == 'month':
                date_condition = "DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')"
            elif period == 'week':
                date_condition = "YEARWEEK(create_time) = YEARWEEK(NOW())"
            elif period == 'day':
                date_condition = "DATE(create_time) = CURDATE()"
            else:
                date_condition = "1=1"  # 默认查询所有
            
            query = f"""
            SELECT 
                g.goods_id,
                g.goods_name,
                g.goods_price,
                SUM(od.quantity) as total_sales,
                COUNT(DISTINCT o.order_id) as order_count
            FROM goods g
            LEFT JOIN order_detail od ON g.goods_id = od.goods_id
            LEFT JOIN orders o ON od.order_id = o.order_id
            WHERE {date_condition}
            GROUP BY g.goods_id, g.goods_name, g.goods_price
            ORDER BY total_sales DESC
            LIMIT %s
            """
            
            results = self.execute_query(query, [limit])
            
            # 确保返回的是字典列表
            if isinstance(results, list):
                return results
            else:
                logger.error(f"查询结果类型错误: {type(results)}")
                return []
                
        except Exception as e:
            logger.error(f"获取热销商品排行失败: {str(e)}")
            return []
    
    def get_sales_curve(self, period: str = 'month', granularity: str = 'day') -> List[Dict]:
        """获取销售曲线数据"""
        try:
            # 根据时间段和粒度构建查询
            if period == 'month':
                if granularity == 'day':
                    group_by = "DATE(create_time)"
                    select_field = "DATE(create_time) as date"
                elif granularity == 'week':
                    group_by = "YEARWEEK(create_time)"
                    select_field = "YEARWEEK(create_time) as date"
                else:
                    group_by = "DATE(create_time)"
                    select_field = "DATE(create_time) as date"
                
                date_condition = "DATE_FORMAT(create_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')"
            else:
                group_by = "DATE(create_time)"
                select_field = "DATE(create_time) as date"
                date_condition = "1=1"
            
            query = f"""
            SELECT 
                {select_field},
                SUM(total_amount) as sales_amount,
                COUNT(*) as order_count
            FROM orders
            WHERE {date_condition}
            GROUP BY {group_by}
            ORDER BY date
            """
            
            results = self.execute_query(query)
            
            # 确保返回的是字典列表
            if isinstance(results, list):
                return results
            else:
                logger.error(f"查询结果类型错误: {type(results)}")
                return []
                
        except Exception as e:
            logger.error(f"获取销售曲线数据失败: {str(e)}")
            return []
    
    def execute_custom_query(self, query: str, params: tuple = None, *args, **kwargs):
        """执行自定义查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor(dictionary=True)
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchall()
            cursor.close()
            connection.close()
            
            return result if result else []
        except Exception as e:
            print(f"查询错误: {e}")
            return []
    
    def execute_query(self, query: str, params: tuple = None, *args, **kwargs):
        """执行查询"""
        return self.execute_custom_query(query, params, *args, **kwargs)
    
    def count_query(self, query: str, params: tuple = None, *args, **kwargs):
        """计数查询"""
        try:
            import mysql.connector
            connection = mysql.connector.connect(**self.db_config)
            cursor = connection.cursor()
            
            if params:
                cursor.execute(query, params)
            else:
                cursor.execute(query)
            
            result = cursor.fetchone()
            cursor.close()
            connection.close()
            
            return result[0] if result else 0
        except Exception as e:
            print(f"计数错误: {e}")
            return 0
